package com.mairuide.casemanage.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.hibernate.SQLQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.mairuide._frame.utils.StringUtils;
import com.mairuide._frame.utils.Testtrans;
import com.mairuide.casemanage.dao.CaseManageDao;
import com.mairuide.casemanage.pojo.CaseManage;
import com.mairuide.entity.PageResult;

/**
 * 服务层
 * 
 * @author 刘中华
 *
 */
@Service
public class CaseManageService {

	@Autowired
	private CaseManageDao caseDao;

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@PersistenceContext
	private EntityManager entityManager;
	/**
	 * 查询全部列表
	 * @return
	 */
	public List<CaseManage> findAll() {
		return (List<CaseManage>)caseDao.findAll();
	}


	/**
	 * 查询列表根据权限和创建人
	 * @return
	 */
	public List<CaseManage> getCaseuuidList(Integer case_authority,Integer create_id) {
		return (List<CaseManage>)caseDao.findByCaseAuthorityOrCreateId(create_id);
	}
	
	/**
	 * 条件查询+分页
	 * @param whereMap
	 * @param page
	 * @param size
	 * @return
	 */
	public Page<CaseManage> findSearch(Map whereMap, int page, int size,Integer createId) {
		Sort sort = new Sort(Sort.Direction.DESC,"id");
		Specification<CaseManage> specification = createSpecification(whereMap,createId);
		PageRequest pageRequest = new PageRequest(page,size,sort);
		return caseDao.findAll(specification, pageRequest);
	}
	
	/**
	 * 条件查询
	 * @param whereMap
	 * @return
	 */
	public List<CaseManage> findSearch(Map whereMap,Integer createId) {
		Specification<CaseManage> specification = createSpecification(whereMap, createId);
		return caseDao.findAll(specification);
	}

	/**
	 * 根据ID查询实体
	 * @param id
	 * @return
	 */
	public CaseManage findById(Integer id) {
		return caseDao.findOne(id);
	}
	
	/**
	 * 根据uuid查询案件信息
	 * @return
	 */
	public CaseManage findByCaseUuid(String caseUuid) {
		return caseDao.findByCaseUuid(caseUuid);
	}
	/**
	 * 增加
	 * @param case
	 */
	public void add(CaseManage caseManage) {
		caseDao.save(caseManage);
	}

	/**
	 * 修改
	 * @param case
	 */
	public void update(CaseManage caseManage) {
		caseDao.save(caseManage);
	}

	/**
	 * 删除
	 * @param id
	 */
	public void deleteById(Integer id) {
		caseDao.delete(id);
	}

	/**
	 * 动态条件构建
	 * @param searchMap
	 * @return
	 */
	private Specification<CaseManage> createSpecification(Map searchMap,Integer createId) {

		return new Specification<CaseManage>() {

			@Override
			public Predicate toPredicate(Root<CaseManage> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
				List<Predicate> predicateList = new ArrayList<Predicate>();
                // 案件名称
                if (searchMap.get("case_name")!=null && !"".equals(searchMap.get("case_name"))) {
                	predicateList.add(cb.like(root.get("case_name").as(String.class), "%"+(String)searchMap.get("case_name")+"%"));
                }
                // 案件编号uuid类型
                if (searchMap.get("case_uuid")!=null && !"".equals(searchMap.get("case_uuid"))) {
                	predicateList.add(cb.equal(root.get("case_uuid").as(String.class), (String)searchMap.get("case_uuid")));
                }
				// 案件编号类型
				if (searchMap.get("case_type")!=null && !"".equals(searchMap.get("case_type"))) {
					predicateList.add(cb.equal(root.get("case_type").as(String.class), (String)searchMap.get("case_type")));
				}
                // 案发辖区
                if (searchMap.get("case_region")!=null && !"".equals(searchMap.get("case_region"))) {
                	predicateList.add(cb.like(root.get("case_region").as(String.class), "%"+(String)searchMap.get("case_region")+"%"));
                }
                // 案件被害人
                if (searchMap.get("case_victim")!=null && !"".equals(searchMap.get("case_victim"))) {
                	predicateList.add(cb.like(root.get("case_victim").as(String.class), "%"+(String)searchMap.get("case_victim")+"%"));
                }
                // 备注
                if (searchMap.get("case_remark")!=null && !"".equals(searchMap.get("case_remark"))) {
                	predicateList.add(cb.like(root.get("case_remark").as(String.class), "%"+(String)searchMap.get("case_remark")+"%"));
                }
                
                if (searchMap.get("keyword")!=null && !"".equals(searchMap.get("keyword"))) {
                	Predicate or1 = cb.or(cb.and(cb.like(root.get("case_name").as(String.class),  "%"+(String)searchMap.get("keyword")+"%")), 
                			cb.and(cb.like(root.get("case_uuid").as(String.class),  "%"+(String)searchMap.get("keyword")+"%")), 
                			cb.and(cb.like(root.get("case_type").as(String.class),  "%"+(String)searchMap.get("keyword")+"%")), 
                			cb.and(cb.like(root.get("case_region").as(String.class),  "%"+(String)searchMap.get("keyword")+"%")), 
                			cb.and(cb.like(root.get("case_victim").as(String.class),  "%"+(String)searchMap.get("keyword")+"%")), 
                			cb.and(cb.like(root.get("case_victim").as(String.class),  "%"+(String)searchMap.get("keyword")+"%")), 
                			cb.and(cb.like(root.get("case_remark").as(String.class),  "%"+(String)searchMap.get("keyword")+"%")));
                	predicateList.add(or1);
                }
				Predicate or = cb.or(cb.and(cb.equal(root.get("case_authority").as(String.class), "0")), cb.and(cb.equal(root.get("create_id").as(String.class), String.valueOf(createId))));
				predicateList.add(or);
                return cb.and( predicateList.toArray(new Predicate[predicateList.size()]));

			}
		};

	}
	//插入新建的表，表名是案件id
	public void createTable(long l){
		Date d = new Date();
		String s = StringUtils.formatDate(d, "yyyyMMddHHmmss");
		String sql="CREATE TABLE \"TB_"+l+"\" \n" +
				"   (\t\"BILL_ID\" NUMBER NOT NULL ENABLE, \n" +
				"\t\"ACCOUNT_BODY\" VARCHAR2(255), \n" +
				"\t\"ACCOUNT_BODY_NUMBER\" VARCHAR2(255), \n" +
				"\t\"ACCOUNT_BODY_CARD\" VARCHAR2(255), \n" +
				"\t\"SIGN\" NUMBER, \n" +
				"\t\"TRADE_DATE\" TIMESTAMP (6), \n" +
				"\t\"ACCOUNT_RIVAL\" VARCHAR2(255), \n" +
				"\t\"ACCOUNT_RIVAL_NUMBER\" VARCHAR2(255), \n" +
				"\t\"ACCOUNT_RIVAL_CARD\" VARCHAR2(255), \n" +
				"\t\"ACCOUNT_MONEY\" NUMBER(20,2), \n" +
				"\t\"SURPLUS_MONEY\" NUMBER(20,2), \n" +
				"\t\"CONTENT_ABSTRACT\" VARCHAR2(255), \n" +
				"\t\"ACCOUNT_BODY_BANK\" VARCHAR2(255), \n" +
				"\t\"ACCOUNT_RIVAL_BANK\" VARCHAR2(255), \n" +
				"\t\"ACCOUNT_ADDRESS\" VARCHAR2(255), \n" +
				"\t\"ACCOUNT_MODE\" VARCHAR2(255), \n" +
				"\t\"SERIAL_NUMBER\" VARCHAR2(255), \n" +
				"\t\"MAC_ADDRESS\" VARCHAR2(255), \n" +
				"\t\"IP_ADDRESS\" VARCHAR2(255), \n" +
				"\t\"CURRENCY\" VARCHAR2(255), \n" +
				"\t\"REMARKS\" VARCHAR2(255), \n" +
				"\t\"CASEMANAGE_ID\" VARCHAR2(100), \n" +
				"\t\"CREATE_ID\" NUMBER, \n" +
				"\t\"TRADING_ORGANIZATION_NUMBER\" VARCHAR2(255), \n" +
				"\t\"TELLER_NUMBER\" VARCHAR2(255), \n" +
				"\t\"INSTITUTION_NUMBER\" VARCHAR2(255), \n" +
				"\t\"CREATE_TIME\" TIMESTAMP (6), \n" +
				"\t\"IS_DELETE\" NUMBER DEFAULT 0, \n" +
				"\t\"TEMPLATE_TAG\" VARCHAR2(255), \n" +
				"\t\"ID_NUMBER\" VARCHAR2(50), \n" +
				"\t\"UUID\" VARCHAR2(255), \n" +
				"\t\"BODY_TYPE\" VARCHAR2(100)\n" +
				"   ) SEGMENT CREATION IMMEDIATE \n" +
				"  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING\n" +
				"  STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645\n" +
				"  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)\n" +
				"  TABLESPACE \"BILL_DB\"";
		String sql2 = "create bitmap index BIT_"+s+" on TB_"+l+" (CASEMANAGE_ID)\r\n" + 
				"  tablespace BILL_DB\r\n" + 
				"  pctfree 10\r\n" + 
				"  initrans 2\r\n" + 
				"  maxtrans 255\r\n" + 
				"  storage\r\n" + 
				"  (initial 64K\r\n" + 
				"    next 1M\r\n" + 
				"    minextents 1\r\n" + 
				"    maxextents unlimited\r\n" + 
				"  )";
		String sql3 = "alter table TB_"+l+"\r\n" + 
				"  add constraint PK_"+s+" primary key (BILL_ID)\r\n" + 
				"  using index \r\n" + 
				"  tablespace BILL_DB\r\n" + 
				"  pctfree 10\r\n" + 
				"  initrans 2\r\n" + 
				"  maxtrans 255\r\n" + 
				"  storage\r\n" + 
				"  (initial 64K\r\n" + 
				"    next 1M\r\n" + 
				"    minextents 1\r\n" + 
				"    maxextents unlimited\r\n" + 
				"  )";

		jdbcTemplate.execute(sql);
		jdbcTemplate.execute(sql2);
		jdbcTemplate.execute(sql3);
	}
	//删除表
	public void delTable(String l){
		String sql="drop table  tb_"+l;
		jdbcTemplate.execute(sql);
	}
	/**
	 * 根据案件id删除导入临时表
	 * @param caseId
	 */
	public void deleteTempTable(String caseId) {
		StringBuffer dropTableSql = new StringBuffer();
		dropTableSql.append("select count(*) AS COUNT from user_tables where table_name = 'TEMP_"+caseId+"'");
		Map<String,Object> m = jdbcTemplate.queryForMap(dropTableSql.toString());
		//判断临时表是否存在
		if(null != m && Integer.parseInt(m.get("COUNT").toString()) > 0) {
			String sql2="drop table  TEMP_"+caseId;
			jdbcTemplate.execute(sql2);
		}
	}

	/**
	 * @Author 刘中华
	 * @Date 2019/3/29 10:50
	 * @Param [casemanageId]
	 * @return java.lang.String
	 * @Description 判断是否分表，返回对应的表名称
	 **/
	public  String getWhichTable(String casemanageId){
		String sql="select * from TB_CASE_MANAGE where CASE_UUID='"+casemanageId+"'";
		List<CaseManage> caseManageList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<CaseManage>(CaseManage.class));
		if (caseManageList!=null&&caseManageList.size()>0&&caseManageList.get(0).getWhich_table()!=null&&caseManageList.get(0).getWhich_table()==1){
			return "tb_"+casemanageId;
		}else{
			return "tb_deal_flow";
		}
	}
	
	/**
	 * 分页查询案件信息
	 * @param keyword
	 * @param page
	 * @param rows
	 * @param integer
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public PageResult<CaseManage> findCaseManageByPage(String keyword,Integer page,Integer rows,Integer creaeteId,String sort,String order) {
		StringBuffer selectSql = new StringBuffer();
		selectSql.append("select t.id as id,t.case_name as case_name,t.case_uuid as case_uuid,"); 
		selectSql.append("t.case_type as case_type,t.case_time as case_time,t.case_region as case_region,"); 
		selectSql.append("t.case_victim as case_victim,t.case_authority as case_authority,t.case_remark as case_remark,");
		selectSql.append("t.create_time as create_time,t.person_type as person_type,t.create_id as create_id,t.which_table as which_table"); 
		StringBuffer fromWhereSql = new StringBuffer();
		fromWhereSql.append(" from tb_case_manage t ");
		fromWhereSql.append("where (cast(t.case_authority as varchar2(255 char)) = '0' ");
		fromWhereSql.append("or cast(t.create_id as varchar2(255 char)) = '"+creaeteId+"' ) ");
		if(StringUtils.isNotEmpty(keyword)) {
			fromWhereSql.append("and (t.case_uuid like '%"+keyword+"%' or t.case_name like '%"+keyword+"%') ");
		}
		if(StringUtils.isNotEmpty(sort)) {
			fromWhereSql.append("order by "+sort+" "+order);
		}else {
			fromWhereSql.append("order by t.id desc");
		}
		long count = jdbcTemplate.queryForObject("select count(1) from ("+selectSql.toString()+fromWhereSql.toString()+") a ",long.class);
		Query query = entityManager.createNativeQuery(selectSql.toString()+fromWhereSql.toString());
		//将查询结果集转为Map
		query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(CaseManage.class));		//设置分页
		query.setFirstResult((page - 1) * rows);
		query.setMaxResults(rows);
		//获取查询结果集
		List<CaseManage> list = query.getResultList();
		PageResult<CaseManage> pageResult = new PageResult(count,list);
		return pageResult;
	}
}
